Homework 3

Author

Christopher Torres

Published

February 16, 2025

Link to my GITHUB Repo: https://github.com/cmsc-vcu/cmsc408-sp2025-hw3-serrotrehpotsirhc

Introduction

In this project, we explore Entity-Relationship (ER) modeling by analyzing three real-world database scenarios: a University Course Enrollment System, a Hospital Patient Management System, and a Movie Theater Booking System.

The objective is to define entities, attributes, and relationships for each scenario, visually represent them using Chen’s Notation and Crow’s Foot Notation, and translate them into relational schemas.

These steps reflect the progression from conceptual design to logical database structuring, which is a critical process in database development.

I will also be describing design choices for each, following the Sports Tournament Management System model provided.

Beyond diagramming, this project also emphasizes design choices related to normalization, cardinality, and relationship modeling.

Homework 3 will provide us more experience using Graphiz and Mermaid, as well as allowing us to begin to understand how to realize conceptual models in ER modelling and beyond! Homework 3 continues to get us familiar with these databases tools and lets us use our skills on our own examples this time.

Technologies Used

  • Graphviz for Chen’s Notation
  • Mermaid for Crow’s Foot Notation

Models

Below are three entity-relation models for systems in the world around me.

Model 1 - University Course Enrollment System

Consider a university that wants to manage students, courses, and professors. Each student has a student ID, name, and major. Courses have a course ID, title, and credit hours. Professors have a professor ID, name, and department. A student can enroll in multiple courses, and each course can have multiple students. A professor can teach multiple courses, but each course is taught by a single professor.

Entities:

  1. Students
  2. Courses
  3. Professors

Schema:

  1. Each student has: a student ID, name, and major.
  2. Each course has: a course ID, title, and credit hours.
  3. Each professor has: a professor ID, name, and department.

Cardinality:

  1. A student can enroll in multiple courses, and each course can have multiple students.

  2. A professor can teach multiple courses, but each course is taught by a single professor.

Design choices

Why These Entities?

  1. Student → Central to any university enrollment system.

  2. Course → The core offering students enroll in.

  3. Professor → Faculty members who teach courses.

Why These Cardinalities:

  1. Students to Courses (Many-to-Many):

A student can enroll in multiple courses, and a course can have multiple students.

  1. Professors to Courses (One-to-Many):

A professor can teach multiple courses, but each course is taught by a single professor.

Why These Participations:

  1. Students → Courses (Partial Participation):

Not all students may enroll in a course.

  1. Courses → Students (Total Participation):

A course must have at least one student enrolled (otherwise, it shouldn’t exist).

  1. Professors → Courses (Total Participation):

Every course must be assigned a professor.

  1. Courses → Professors (Partial Participation):

A professor may teach multiple courses, but they are not required to teach any.

Chen Diagram

ER student student name0 name student--name0 studentID studentID student--studentID major major student--major Enrolls Enrolls student--Enrolls m course course courseID courseID course--courseID title title course--title credit_hours credit_hours course--credit_hours Teaches Teaches course--Teaches n professor professor name1 name professor--name1 professorID professorID professor--professorID department department professor--department name2 name Enrolls--course n Teaches--professor 1

Crows Foot diagram

erDiagram
    STUDENT {
        int studentID
        string name
        string major
    }
    
    COURSE {
        int courseID
        string title
        int credit_hours
    }
    
    PROFESSOR {
        int professorID
        string name
        string department
    }
    
    STUDENT ||--o{ COURSE : enrolls
    PROFESSOR ||--o{ COURSE : teaches

Relational model

Relations and Attributes:

  1. Student (studentID, name, major)
  2. Course (courseID, title, credit_hours)
  3. Professor (professorID, name, department)

Primary and Foreign Keys:

  1. Student: studentID (PK)
  2. Course: courseID (PK)
  3. Professor: professorID (PK)

Model 2 - Hospital Patient Management System

Consider a hospital that wants to manage patients, doctors, and appointments. Each patient has a patient ID, name, and date of birth. Doctors have a doctor ID, name, and specialty. Appointments have an appointment ID, date, and time. A patient can have multiple appointments, and each appointment involves one patient. A doctor can have multiple appointments, but each appointment is handled by a single doctor.

Entities:

  1. Patients

  2. Doctors

  3. Appointments

Schema:

  1. Each patient has: a patient ID, name, and date of birth.

  2. Each doctor has: a doctor ID, name, and specialty.

  3. Each appointment has: an appointment ID, date, and time.

Cardinality:

  1. A patient can have multiple appointments, and each appointment involves one patient.

  2. A doctor can have multiple appointments, but each appointment is handled by a single doctor.

Design choices

Why These Entities?

  1. Patient → Represents individuals receiving medical care.

  2. Doctor → Represents medical professionals providing treatment.

  3. Appointment → The event linking patients and doctors.

Why These Cardinalities:

  1. Patients to Has to Appointments (One-to-Many):

A patient can have multiple appointments, but each appointment is linked to one patient.

  1. Doctors to Handles to Appointments (One-to-Many):

A doctor can have multiple appointments, but each appointment is handled by a single doctor.

Why These Participations:

  1. Patients → Has (Partial Participation):

A patient may not have any appointments.

  1. Appointments → Has (Total Participation):

Every appointment must be linked to a patient.

  1. Doctors → Handles (Partial Participation):

A doctor may not have any scheduled appointments.

  1. Appointments → Handles (Total Participation):

Every appointment must have a doctor assigned.

Chen Diagram

ER patient patient name0 name patient--name0 patientID patientID patient--patientID dob dob patient--dob Has Has patient--Has 1 doctor doctor name1 name doctor--name1 doctorID doctorID doctor--doctorID specialty specialty doctor--specialty appointment appointment appointmentID appointmentID appointment--appointmentID date date appointment--date time time appointment--time Handles Handles appointment--Handles 1 Has--appointment m Handles--doctor m

Crows Foot diagram

erDiagram
    PATIENT {
        int patientID
        string name
        date dob
    }
    
    APPOINTMENT {
        int appointmentID
        date date
        time time
    }
    
    DOCTOR {
        int doctorID
        string name
        string specialty
    }
    
    PATIENT ||--o{ APPOINTMENT : has
    DOCTOR ||--o{ APPOINTMENT : handles

Relational model

Relations and Attributes:

  1. Patient (patientID, name, dob)
  2. Doctor (doctorID, name, specialty)
  3. Appointment (appointmentID, date, time, patientID, doctorID)

Primary and Foreign Keys:

  1. Patient: patientID (PK)

  2. Doctor: doctorID (PK)

  3. Appointment: appointmentID (PK), patientID (FK → Patient), doctorID (FK → Doctor)

Model 3 - Movie Theater Booking System

Consider a movie theater that wants to manage customers, movies, and screenings. Each customer has a customer ID, name, and membership status. Movies have a movie ID, title, and duration. Screenings have a screening ID, date, and time. A customer can attend multiple screenings, and each screening can have multiple customers. A movie can have multiple screenings, but each screening features a single movie.

Entities:

  1. Customers

  2. Movies

  3. Screening

Schema:

  1. Each customer has: a customer ID, name, and membership status.

  2. Each movie has a movie ID, title, and duration

  3. Each screening has a screening ID, date, and time.

Cardinality:

  1. A customer can attend multiple screenings, and each screening can have multiple customers.

  2. A movie can have multiple screenings, but each screening features a single movie.

Design choices

Why These Entities?

  1. Customer → Represents individuals purchasing tickets.

  2. Movie → The content being screened.

  3. Screening → Represents a specific showing of a movie at a given time.

Why These Cardinalities:

  1. Customers to Books to Screenings (Many-to-Many):

A customer can book multiple screenings, and each screening can have multiple customers.

  1. Movies to Scheduled to Screenings (One-to-Many):

A movie can have multiple screenings, but each screening is for a single movie.

Why These Participations:

  1. Customers → Books (Partial Participation):

A customer may not book any screenings.

  1. Screenings → Books (Total Participation):

A screening must have at least one customer booked.

  1. Movies → Scheduled (Total Participation):

A movie must be scheduled for at least one screening.

  1. Screenings → Scheduled (Partial Participation):

Each screening must be linked to a single movie.

Chen Diagram

ER customer customer name0 name customer--name0 customerID customerID customer--customerID membership_status membership_status customer--membership_status Books Books customer--Books m movie movie movieID movieID movie--movieID title title movie--title duration duration movie--duration screening screening screeningID screeningID screening--screeningID date date screening--date time time screening--time Scheduled Scheduled screening--Scheduled n name1 name Books--screening n Scheduled--movie 1

Crows Foot diagram

erDiagram
    CUSTOMER {
        int customerID
        string name
        string membership_status
    }
    
    MOVIE {
        int movieID
        string title
        int duration
    }
    
    SCREENING {
        int screeningID
        date date
        time time
    }
    
    CUSTOMER ||--o{ SCREENING : books
    MOVIE ||--o{ SCREENING : scheduled

Relational model

Relations And Attributes:

  1. Customer (customerID, name, membership_status)

  2. Movie (movieID, title, duration)

  3. Screening (screeningID, date, time, movieID)

  4. Booking (customerID, screeningID) → (Bridge table for many-to-many relationship)

Primary And Foreign Keys:

  1. Customer: customerID (PK)

  2. Movie: movieID (PK)

  3. Screening: screeningID (PK), movieID (FK → Movie)

  4. Booking: customerID (FK → Customer), screeningID (FK → Screening) (Composite PK)

README

Welcome to Homework 3

Project Overview

This project explores Entity-Relationship (ER) modeling by defining and visualizing three real-world database scenarios. The project includes:

  • Scenario Design: Defining three database scenarios with entities, attributes, and relationships.
  • Graphical Representation: Using Chen’s Notation (Graphviz) and Crow’s Foot Notation (Mermaid) to model each scenario.
  • Relational Model Construction: Converting each ER model into relational schemas.
  • Design Choices Explanation: Documenting the rationale behind modeling decisions.

Scenarios Modeled

  1. University Course Enrollment System
    • Models students, courses, and professors.
    • Defines enrollment and teaching relationships.
  2. Hospital Patient Management System
    • Represents patients, doctors, and appointments.
    • Captures how appointments are scheduled and handled.
  3. Movie Theater Booking System
    • Covers customers, movies, and screenings.
    • Models how customers book screenings.

Graphical Representations

Each scenario is modeled in two notations:

  • Chen’s Notation → Illustrates entities, attributes, and relationships with diamonds.
  • Crow’s Foot Notation → Shows relationships with cardinality symbols for better database structuring.

Relational Models

Each ER model was transformed into relational schemas with:

  • Tables & Attributes
  • Primary and Foreign Keys
  • Bridge Tables (where necessary for many-to-many relationships)

Design Choices

Normalization & Cardinality

  • Many-to-Many Relationships (e.g., students enrolling in courses, customers booking screenings) were handled using bridge tables to avoid redundancy.
  • One-to-Many Relationships (e.g., doctors handling appointments, professors teaching courses) were directly mapped using foreign keys to maintain referential integrity.

Graphviz vs. Mermaid

  • Graphviz (Chen’s Notation) was used for detailed conceptual modeling, making attribute representation clearer.
  • Mermaid (Crow’s Foot Notation) was chosen for logical database design, which aligns more closely with relational modeling.

Why These Scenarios?

  • They represent common real-world database applications used in education, healthcare, and entertainment.
  • They demonstrate different levels of complexity, from simple one-to-many relationships (hospital system) to complex many-to-many relationships (course enrollment).

Quarto will render report.qmd in the reports folder into the report.html file.

Further examples found in the example folder!

Thank you! - Christopher Torres

Reflection

What was the most surprising (good or bad) thing that you learned by doing this assignment?

The most surprising thing I learned from Homework 3 was the amount of work and how important it is to have a great database design from the beginning. When we are first creating database designs from our own examples, having effienct conceptions of the problem and efficient relationships in the problem is essential for creating a database which is efficient itself. Making sure this step is clear in the process, which is efficientlay crafting databases from the beginning is crucial in database desing. I am glad I got practice learning ER modeling from my own examples.

How much time did you spend on this assignment?

I spend roughly 1 hour and 45 minutes on this assignment since its structure was already pretty similar to Homework 2.

What CS classes have you had that the quarto math expressions and diagrams would have made it easier?

The only classes I can think of which it would have made life easier might have been my website design and development class as well as Algorithms & Data Structures, which I took back in 2022. It also might have been useful in my Discrete Math course.